import pandas as pd

from models import Data
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Load the Excel file
file_path = '文献信息-V1.xlsx'
data_df = pd.read_excel(file_path)

data_df = data_df[data_df['simle'].notna()]

# Replace NaN in 'title' and 'doi'
last_valid_title = None
last_valid_doi = None
for idx, row in data_df.iterrows():
    if pd.isna(row['title']):
        data_df.at[idx, 'title'] = last_valid_title
    else:
        last_valid_title = row['title']

    if pd.isna(row['doi']):
        data_df.at[idx, 'doi'] = last_valid_doi
    else:
        last_valid_doi = row['doi']

# 连接到数据库
db_uri = 'sqlite:///./data.db'
engine = create_engine(db_uri)

Session = sessionmaker(bind=engine)
session = Session()

for _, row in data_df.iterrows():
    new_record = Data(
        title=row['title'],
        doi=row['doi'],
        simle=row['simle'],
        radiance=row['radiance'],
    )
    session.add(new_record)

# 提交会话
session.commit()
